/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package hecatone;

/**
 *
 * @author fredericpaquette
 */
import java.sql.*;
import java.util.Hashtable;
import java.util.Vector;
import java.util.Enumeration;
import java.text.MessageFormat;
import org.apache.log4j.Logger;
import org.apache.log4j.Priority;
import org.apache.log4j.PropertyConfigurator;


public class SqlCommands {
    private Hashtable gameproperties;
    private static final Logger logger = Logger.getLogger(SqlCommands.class);
    Connection dbconnection;
    GameMessageBundle message;
    PreparedStatement updateOrdreEtat;
    ResultSet tour;
    
    PreparedStatement ordres,characters,squads;
    
    public SqlCommands(Hashtable gameprop)
    {
        setGameProp(gameprop);
        PropertyConfigurator.configure("log4j.properties");
        dbconnection = createDatabaseConnection();
        logger.info("SQL access completed");
        message = new GameMessageBundle();
        initPrepStatements();
        tour = getCurrentTurn();
        init();
    }
    
   private Connection createDatabaseConnection()
   {
       Connection conn = null;
       try
       {
           Class.forName ("com.mysql.jdbc.Driver").newInstance ();
           conn = DriverManager.getConnection ((String)gameproperties.get("dburl"), (String)gameproperties.get("dbuser"), (String)gameproperties.get("dbpassword"));
           logger.info("Connected to database " + (String)gameproperties.get("dburl") );
       }
       catch (Exception e)
       {
           logger.error(e.getMessage());
           logger.error("Failed to connect to database exiting");
           logger.debug(e.getStackTrace());
           System.exit(1);
       }
       return conn;
       
   } 
   /*
    * Returns a all the orders of value X for the current tick
    */
   
   public ResultSet getOrdersToExecute(int order)
   {
       
      
       ResultSet set = null;
       try{
       ordres = dbconnection.prepareStatement("SELECT o.ORDRE_ID, o.PERSO_ID, p.nom, n.NATION_NAME, p.etat, h.xCoord, h.yCoord, h.HEX_ID, o.code, o.parametre, "
                                                             + "o.tick_prevu, o.tick_restant, min(o.sequence) AS sequence "
                                                             + "FROM perso p, hex h, ordre o, nation n "
                                                             + "WHERE p.HEX_ID=h.HEX_ID and p.PERSO_ID=o.PERSO_ID AND p.NATION_ID=n.NATION_ID AND o.tick_restant>0 AND o.code="+order 
                                                             + " GROUP BY p.PERSO_ID order by RAND()");
       ordres.execute();
       set = ordres.getResultSet();
       logger.debug("Pulled set from DB "+ set +" Order number:"+ order);
      
        }
       catch(SQLException sqle){
           logger.error(sqle.getMessage());
           logger.debug(sqle.getStackTrace());
        }
       return set;
   }
   /*******************
    * TestMoveDirection, will use a stored procedure in mysql to chack the validity of the next hex
    * The hex has to exist, if the hex does not exist this procedure will return a 0
    * Deprecated for now (2013/06) part of the original code, replaced by new code in hecatone.java
    * @param direction
    * @return integer
    */
   
 /*   public int testMovDirection(String direction,int hexid)
   {
       PreparedStatement stm1;
       PreparedStatement stm2;
       ResultSet set;
       logger.info("Checking validity of the next hex");
       try{
           stm1 = dbconnection.prepareStatement("CALL GetHexDir(?, ?, @h2, @err)");
           stm2 = dbconnection.prepareStatement("SELECT @h2 AS HEX_ID2, @err AS erreur");
           stm1.setInt(1, hexid);
           stm1.setString(2, direction);
           stm1.execute();
           logger.debug("Stored procedure executed \"GetHexDir\"");
           stm2.execute();
           set = stm2.getResultSet();
           if (set == null)
           {
               logger.debug("Empty result set");
           }
           ResultSetMetaData data = set.getMetaData();
           set.next();
           if(set.getInt("erreur")==0)
           {
               logger.info("A valid hex has been found");
               return (set.getInt(1));
           }
           else
           {
               logger.info("Looks like this hex is not valid");
               return 0;
           }
       }
       catch(SQLException sqle){
           logger.error("A database error has occured");
           logger.error(sqle.getMessage());
           sqle.printStackTrace();
           return 0;
       }
   }*/
   
    public boolean updateRemainingTick(int order,int remaintick)
    {
        PreparedStatement update;
        
        try{
            logger.debug("Updating tick for order ID = "+ order);
            update = dbconnection.prepareStatement("update ordre set tick_restant= ? where ORDRE_ID = ?");
            update.setInt(1, remaintick - 1);
            update.setInt(2, order);
            update.execute();
            return true;
        }
        catch(SQLException sqle)
        {
            logger.error("Database problem in update remaining tick");
            logger.error(sqle.getMessage());
            return false;
        }
    }
    /**********************************************************************************************************
     * 
     * @param order
     * @return 
     * Move character will move a character to the hex given by the nexthex parameter. If the characted commands
     * an army, the army position will be updated at the same time. The next hex has to be valid for both character
     * and army. if the move is successful, the function returns true else it returns false. In anycase, the function
     * will also update table 'ordre_etat'
     *********************************************************************************************************/
    
    public boolean moveCharacter(int persoid,int newhex,int orderid)
    {
        try{
            PreparedStatement stm1 = dbconnection.prepareStatement("SELECT * FROM perso where perso_id= ? and tour=(select max(tour) from perso where perso_id = ? )"+
                                                                    " and tick=(select max(tick) from perso where perso_id =? and tour ="+
                                                                    " (select max(tour) from perso where perso_id = ?));");
            PreparedStatement stm2 = dbconnection.prepareStatement("select * from armee where COM_ID = "+ persoid);
            PreparedStatement stm3 = dbconnection.prepareStatement("CALL IsMovementOK(850, ?, 'E', @err);");
            PreparedStatement stm4 = dbconnection.prepareStatement("SELECT @err AS erreur");
            /*Ici, tu dois remplacer les updates par des inserts... pis ça devrait marcher */
            PreparedStatement stm5 = dbconnection.prepareStatement("update perso set TOUR = "+tour.getInt("TURN")+" ,TICK = "+tour.getInt("TICK")+",HEX_ID = "+newhex +" where PERSO_ID ="+ persoid);
            PreparedStatement stm6 = dbconnection.prepareStatement("update armee set TOUR = "+tour.getInt("TURN")+" , HEX_ID = "+newhex +" where COM_ID = "+persoid);
            logger.debug("Prepared statements completed for move characters");
            logger.debug("Will first test if characted is commanding an army");
            stm1.setInt(1, persoid);
            stm1.setInt(2, persoid);
            stm1.setInt(3, persoid);
            stm1.setInt(4, persoid);
            ResultSet set1 = stm1.executeQuery();
            set1.next();
            ResultSet set2;
    
            if(set1.getBoolean("chef_armee"))
            {
                logger.debug(set1.getString("NOM")+" Is leading an army");
                set2 = stm2.executeQuery();
                set2.next();              
                logger.debug("Is the move ok for an army?");
                stm3.setInt(1, newhex);
                ResultSet set4 = stm4.executeQuery();
                set4.next();
                logger.debug("isMovementOK has returned: "+set4.getBoolean("erreur"));
                if(set4.getBoolean("erreur"))
                {
                    logger.debug("Based on stored procedure isMovementOK, It is apparently not legal to move the army here");
                    logger.debug("Updating the ORDRE_ETAT table");
                    String r = MessageFormat.format(message.getMsg("armymovefailure"),set1.getString("NOM"),newhex);
                    logger.debug(r);
                    updateOrderEtat(r,orderid);
                }
                else
                {
                    logger.info("Lets move this army to: "+ newhex);
                    stm5.execute();
                    stm6.execute();
                    logger.debug("Army has been moved to: "+ newhex+ " now updating ORDRE_ETAT");
                    String r = MessageFormat.format(message.getMsg("armymovesuccess"),set1.getString("NOM"),newhex);
                    updateOrderEtat(r,orderid);
                }
            }
            }catch(SQLException sqle)
        {
            logger.error("Database error in moveCharacter");
            logger.error(sqle.getMessage());
            if(logger.isDebugEnabled())
            {
                sqle.printStackTrace();
            }
            return false;
        }
        
       return true;
    }
    
    public void updateOrderEtat(String result,int orderid)
    {
        try{
        updateOrdreEtat.setString(1, result);
        updateOrdreEtat.setInt(2, tour.getInt("TICK"));
        updateOrdreEtat.setInt(3, tour.getInt("TURN"));
        updateOrdreEtat.setInt(4, orderid);
        updateOrdreEtat.execute();
        }
        catch(SQLException sqle)
        {
            logger.error("DB error in updateOrderEtat");
            logger.error(sqle.getMessage());
        }
    }
   
    public void setGameProp(Hashtable gp) 
    {
        gameproperties = gp;
    }
    private void initPrepStatements()
    {
        try{
            updateOrdreEtat = dbconnection.prepareStatement("update ordre_etat set RESULTAT = ?, TICK = ?, TOUR = ? where ORDRE_ID = ?");
        }
        catch (SQLException sqle)
        {
            logger.error("DB error in initPrepStatement");
            logger.error(sqle.getMessage());
        }
    }
    
    
    /*
     * Return the number of the current turn
     * 
     */
    public int getTurnNumber()
    {
     
        ResultSet t = this.getCurrentTurn();
        try {
            return t.getInt("TURN");
            
        }
        catch(SQLException sqle)
        {
            logger.error("Database error in getTurnNumber "+sqle.getMessage());
            return 0;
        }
    }
    /*
     * Same here but will return the result set including the tick from the database;
     */
    
    public ResultSet getCurrentTurn()
    {
        try{
            PreparedStatement stat = dbconnection.prepareStatement("select * from TOUR",ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_UPDATABLE);
            stat.execute();
            ResultSet set = stat.getResultSet();
            set.next();
            return set;
        }
        catch (SQLException sqle)
                {
                    logger.error("Failed to get current turn from database");
                    logger.error(sqle.getMessage());
                    return null;
                }
    }
    public void updateTurnTick()
    {
        try{
        int currentturn = tour.getInt("TURN");
        int currenttick = tour.getInt("TICK");
        logger.debug("Updating tick and turn, current tick = "+currenttick+" currentturn = "+currentturn);
        if (currenttick >= Integer.parseInt((String)gameproperties.get("tickperturn")))
        {
            tour.updateInt("TICK", 1);
            tour.updateInt("TURN",currentturn + 1);
            tour.updateRow();
        }
        else{
            logger.debug("Updating only the tick this time");
            tour.updateInt("TICK", currenttick + 1);
            tour.updateRow();
          }
        }
        catch(SQLException sqle)
        {
            logger.error("Database error in updateTurnTick");
            logger.error(sqle.getMessage());
            if(logger.isDebugEnabled())
            {
                sqle.printStackTrace();
            }
        }
    }
    public void updatePerso(ResultSet set,int[] keys, Object[] values)
    {
        try{
            PreparedStatement stm1 = dbconnection.prepareStatement("insert into perso (perso_id,tour,tick,nom,nation_id,commandeur,"
                                                              +"agent,mage,sante,defi,hex_id,etat,sur_cdep,acc_armee,chef_armee)"
                                                              +" values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
            stm1.setInt(1, set.getInt("perso_id"));
            
            for(int i=0;i>=keys.length;i++)
            {
                
            }   
                 
            
        }
        catch (SQLException sqle)
                {
                    
                }
    }   
    
    
    /****
     * 
     * @param hex
     * @return ResultSet
     * 
     * This method is the main method to understand if an army attempting to move out of a hex can.
     * 
     * Two things can prevent it from moving:
     * A ennemy or neutral army blocking in the direction where the armie is moving
     * or an ennemy army or neutral that has just entered the hex in the opposite direction.
     * 
     * The method will return a set of all armies blocking the way of the army attempting to move.
     */
    public ResultSet checkForArmies (int hex)
            
    {
        logger.info("Checking for armies in the hex");
        PreparedStatement stm;
        ResultSet armies;
        try
        {
            stm = dbconnection.prepareStatement ("select * from armee where hex_id = "+hex);
            armies = stm.executeQuery();
        }
        catch(SQLException sqle) {
            logger.error(" Database error in checkForArmies()");
            logger.error(sqle.getMessage());
            return null;
        }
        
        return armies;
    }
    
    
    /*
     * The only thing that this function does is to check if the hex is part of the map.
     */
    public boolean doesHexExist (int hex)
    {
        PreparedStatement stm;
        ResultSet hexid;
        try{
        stm = dbconnection.prepareStatement("select HEX_ID from hex where hex_id =" + hex);
        hexid = stm.executeQuery();
        if(hexid.next())
        {
            return true;
        }
        else
        {
            return false;
        }
                }
        catch (SQLException sqle)
                {
                    logger.error ("Database error");
                    logger.error (sqle.getMessage());
                    return false;
                }
        
    }     
    /*
     * A blocking com is either entering the hex before the current armie attempt 
     * to move or is blocking the direction of the move. The relative strenght of all armies
     * have to be calculated to see if there is an overrun or not. An army too weak will be
     * destroyed, including the leading commanders and characters in the army.
     * 
     */
    public Vector findBlockingComs (ResultSet armies)
    {
        
        Vector coms = new Vector (10,1); 
        
        
        return coms;
        
    }
    /**************************************************************************
     * 
     * @param coms
     * @return Hashtable with coms and their armie strenght for this turn.
     * 
     * The armie strenght is calculated based on:
     * 
     * troup types, defense and offense, fierceness, commander level, terrain type
     * nation modifier.
     * 
     * This method may soon become obsolete. We now do everything within objects
     * 
     */
    public Hashtable calculateArmiesStrength(Vector coms, int hex)
    {
        logger.debug("Entering calculate armies strengths");
        
        PreparedStatement type,tm;
        ResultSet set, tt;
        Hashtable armstrength = new Hashtable();
        
        Enumeration i = coms.elements();
        try{
            
            type = dbconnection.prepareStatement("select h.TERRAIN_ID, h.temperature, t.DESCRITION from hex h, TERRAIN_TYPE t where h.HEX_ID = "+hex+" and t.TERRAIN_TYPE = h.TERRAIN_ID");
            tt = type.executeQuery();
            tt.next();        
            
            tm = dbconnection.prepareStatement("select "+ tt.getString("DESCRIPTION") +" from TROUP_TYPE where TROUP_TYPE = ?");
            tm.execute();
            
            while (i.hasMoreElements())
            {
                String cm = (String)i.nextElement();
                logger.debug("Calculating army strength for "+ cm);
                set = squads.executeQuery(cm);
                while (set.next())
                {
                    logger.debug("Calculating for army_id "+ set.getInt("ARMY_ID")+" squad_id= "+set.getInt("ID"));
                 //   int strength = calculateSquadStrength (tm.getResultSet().getInt(0),set); 
                    
                    
                }
            }
        }
        catch (SQLException sqle)
        {
            
        }
        
        return armstrength;
    }
    
    /*****************************************************************************
     * 
     * @param terrain_modifier
     * @param squad The result set squad is coming from the prepared statement in the calculateArmyStrenght method above.
     * This method returns the squad defensive strenght including the nation terrain modifier and the terrain mod in the current hex
     * but excluding the commander strength..
     * 
     * @return 
     */
    
    public int getSquadDefensiveValue (int terrain_modifier, ResultSet squad )
    {
        int defense = 0;
        
        
        
        return defense;
    }
    public int getSquadOffensiveValue (int terrain_modifier, ResultSet squad)
    {
        int offense = 0;
        
        return offense;
    }
    
    
    /* This method returns all the characters for this turn in a resultset loaded from the database
     * 
     */
    public ResultSet returnAllCharacters()
    {
        
        try
        {
            characters.execute();
            return characters.getResultSet();
        }
        catch(SQLException sqle)
        {
            logger.info("Database error in returnAllCharacters "+sqle.getMessage());
            return null;
        }
        
    }
    /*
     * This is a simple check to see if a character is leading an army.
     * Note to self, the if statement may not work if badly understood the javadoc.
     */
    public boolean isCharacterArmyLeader (int ID)
    {
        try{
        army.setInt(1, ID);
        army.setInt(2, this.getTurnNumber());
        if(army.execute())
        {
            return false;
            
        }
        else
        {
            return true;
        }
        
        }
        catch (SQLException sqle)
        {
            logger.error("Database error in isCharacterArmyLeader "+ sqle.getMessage());
            return false;
        }
        
        
    }
 
    /*********************************************************************************
     * This method create all the prepared statement to be use in this class
     * 
     ********************************************************************************/
    PreparedStatement army;
    
    public void init()
    {
        try{
        /*
         * Load all Character for the current turn in a result set. This one is mainly use for the creation of 
         * character objects at the beginning of a tour.
         */
        characters = dbconnection.prepareStatement("select * from perso where tour = (select max(tour) from perso)");
        
        
        /*
         * Load all squad belonging to the same army. The result set includes all these attributes needed to 
         * build the squads objects part of an army. The result should be the latest entry based on the max turn
         * from the character table.
         */
        squads = dbconnection.prepareStatement("select a.NATION_ID, a.NATION_NAME, a.TR_TERRAIN1, a.TR_TERRAIN2,"+""
                    + " c.ARMEE_ID, e.ID, e.ARMY_ID, e.TROUP_TYPE, e.DEFENSE, e.ATTACK, e.TRAINING, e.HARDNESS, e.NUMBER,"+""
                    + " e.FIERCE from nation a, armee c, SQUAD e where c.COM_ID = ? and e.ARMY_ID = c.ARMEE_ID and a.NATION_ID = c.NATION_ID and TURN = ?");
        /*
         * Return armies that are ennemy based on the commander, its nation and the current hex. This will be use for blocking armies also for 
         * other type of actions such as recons etc.
         */
        
        
        /*
         * Return all the characters executing an order for the latest tick with the remaining ticks for the current turn
         */
        
        ordres = dbconnection.prepareStatement("SELECT o.ORDRE_ID, o.PERSO_ID, p.nom, n.NATION_NAME, p.etat, h.xCoord, h.yCoord, h.HEX_ID, o.code, o.parametre, "
                                                             + "o.tick_prevu, o.tick_restant, min(o.sequence) AS sequence "
                                                             + "FROM perso p, hex h, ordre o, nation n "
                                                             + "WHERE p.HEX_ID=h.HEX_ID and p.PERSO_ID=o.PERSO_ID AND p.NATION_ID=n.NATION_ID AND o.tick_restant>0 AND o.code= ?"
                                                             + " GROUP BY p.PERSO_ID order by RAND()");
        /*************
         * Check if character is leading an army
         * Simply does a select on table army and attempts to find if the commander is leading an army. The result set should be
         * the army
         * 
         */
        army = this.dbconnection.prepareStatement("select * from armee where COM_ID = ? and turn = ?");
        
        }
        catch(SQLException sqle)
        {
            logger.error("Database error in init: "+sqle.getMessage());
            return;
        }
    }
}
